module.exports = {
  GetShares : `select * from shares.shareinfomation`,
  SaveShare: `insert into shares.shareinfomation(
      \`Code\`, \`Name\`, CreatedOn, PurchaseOn, PurchasePrice, PurchaseQuantity
    )
    values (
      @code, @name, @createdOn, @purchaseOn, @purchasePrice, @purchaseQuantity
    )
    ON DUPLICATE KEY UPDATE \`Name\`=@name, PurchaseOn=@purchaseOn, purchasePrice=@purchasePrice, PurchaseQuantity=@purchaseQuantity`,
  
  SaveDailyDatas: `INSERT INTO shares.dailydatas (
      Code, \`Date\`, \`Time\`, \`Name\`, StartPrice, YesterdayPrice,
      CurrentPrice, MaxPrice, MinPrice, SalleCounts, SalleAmount)
    VALUES (
      @code, @date, @time, @name, @startPrice, @yesterdayPrice,
      @currentPrice, @maxPrice, @minPrice, @salleCounts, @salleAmount)
    ON DUPLICATE KEY UPDATE CurrentPrice=@currentPrice`,
  GetSharesWithPrice: `SELECT 
      a.*,
      p1.YesterdayPrice AS BeginPrice,
      p2.\`Date\` AS CurrentDate,
      p2.CurrentPrice,
      p2.YesterdayPrice
    FROM shares.shareinfomation a
    left join lateral (
      select * from shares.dailydatas b1
      where b1.\`Code\`=a.\`Code\`
      order by b1.\`Date\` ASC, b1.\`Time\` ASC limit 1
    ) p1 on a.\`Code\`=p1.\`Code\`
    left join lateral (
      select * from shares.dailydatas b2
      where b2.\`Code\`=a.\`Code\`
      order by b2.\`Date\` Desc, b2.\`Time\` DESC limit 1
    ) p2 on a.\`Code\`=p2.\`Code\`
    order by PurchaseOn desc, (p2.CurrentPrice - p2.YesterdayPrice) / p2.YesterdayPrice desc;`,
  GetStatistics: `
    with cte as(
      select
        \`Date\`,
        max(\`Time\`) AS \`Time\`
      from shares.dailydatas a
      where a.\`Code\`=@code and a.\`Date\`<sysdate()
      group by floor(date_format(concat(\`Date\`, ' ', \`Time\`), '%Y%m%d%H') div 5), \`Date\`
      union
      select
        \`Date\`,
        max(\`Time\`) AS \`Time\`
      from shares.dailydatas a
      where a.\`Code\`=@code and a.\`Date\`=date(sysdate())
      group by floor(date_format(concat(\`Date\`, ' ', \`Time\`), '%Y%m%d%H%i%s') div @interval), \`Date\`
    )
    SELECT SQL_CALC_FOUND_ROWS
      d.*
    from cte
    inner join shares.dailydatas d on d.\`Code\`=@code and d.\`Date\`=cte.\`Date\` and d.\`Time\`=cte.\`Time\``,
  GetCurrentDateStatistics: `
    with cte as(
      select
        \`Date\`,
        max(\`Time\`) AS \`Time\`
      from shares.dailydatas a
      where a.\`Code\`=@code and a.\`Date\`=date(sysdate())
      group by floor(date_format(concat(\`Date\`, ' ', \`Time\`), '%Y%m%d%H%i%s') div @interval), \`Date\`
    )
    SELECT SQL_CALC_FOUND_ROWS
      d.*
    from cte
    inner join shares.dailydatas d on d.\`Code\`=@code and d.\`Date\`=cte.\`Date\` and d.\`Time\`=cte.\`Time\``,
  GetSharePriceDetail: `select
      a.*,
      p.CurrentPrice
    from(
      select \`Code\`, \`Date\`, max(YesterdayPrice) AS StartPrice
      from shares.dailydatas where \`Code\`=@code
      group by \`Code\`, \`Date\`
    ) a
    left join lateral(
      select \`Code\`, CurrentPrice from shares.dailydatas b2
      where b2.\`Code\`=a.\`Code\` and b2.\`Date\`=a.\`Date\`
      order by b2.\`Date\` Desc, b2.\`Time\` DESC limit 1
    ) p on a.\`Code\`=p.\`Code\``,

  ClearSharesDailyDatas: 'CALL shares.sp_clear_dailydatas()',
  RemoveShare: `delete from shares.shareinfomation where \`Code\`=@code;delete from shares.dailydatas WHERE \`Code\`=@code;`,
  SaveIndex: `insert into shares.indexes(
      \`Name\`, RecordDateTime, \`Index\`, Changes, ChangeRatio, TurnoverVolume
    )
    values (
      @name, @recordDateTime, @index, @changes, @changeRatio, @turnoverVolume
    )
    ON DUPLICATE KEY UPDATE \`Name\`=@name, RecordDateTime=@recordDateTime`,
  GetIndexes: `with cte as(
    select
      Name,
      RecordDateTime,
      round(\`Index\`, 2) as \`Index\`,
      round(Changes, 2) as Changes,
      round(ChangeRatio, 2) as ChangeRatio,
      TurnoverVolume 
    from shares.indexes
    where datediff(RecordDateTime, sysdate())=0
  ),
  minCte as (
    select Name, min(\`Index\`) as MinIndex from cte a1 group by a1.Name
  ),
  maxCte as (
    select Name, max(\`Index\`) as MaxIndex from cte a1 group by a1.Name
  ),
  curCte as (
    select Name, max(RecordDateTime) as MaxRecordDateTime from cte a1 group by a1.Name
  )
  select
    distinct 
    Name,
    RecordDateTime ,
    round(\`Index\`, 2) as \`Index\`,
    round(Changes, 2) as Changes,
    round(ChangeRatio, 2) as ChangeRatio,
    TurnoverVolume
  from shares.indexes
  where datediff(RecordDateTime, sysdate())<0
  union
  select
    a.Name,
    max(a.RecordDateTime) as RecordDateTime,
    min(a.\`Index\`) as \`Index\`,
    min(a.Changes) as Changes,
    min(a.ChangeRatio) as ChangeRatio,
    min(a.TurnoverVolume) as TurnoverVolume
  from cte a
  inner join minCte on a.Name=minCte.Name and a.\`Index\`=minCte.MinIndex
  group by a.Name
  union 
  select
    a1.Name,
    max(a1.RecordDateTime) as RecordDateTime,
    min(a1.\`Index\`) as \`Index\`,
    min(a1.Changes) as Changes,
    min(a1.ChangeRatio) as ChangeRatio,
    min(a1.TurnoverVolume) as TurnoverVolume
  from cte a1
  inner join maxCte on a1.Name=maxCte.Name and a1.\`Index\`=maxCte.MaxIndex
  group by a1.Name
  union 
  select
    a2.Name,
    min(a2.RecordDateTime) as RecordDateTime,
    min(a2.\`Index\`) as \`Index\`,
    min(a2.Changes) as Changes,
    min(a2.ChangeRatio) as ChangeRatio,
    min(a2.TurnoverVolume) as TurnoverVolume
  from cte a2
  inner join curCte on a2.Name=curCte.Name and a2.RecordDateTime=curCte.MaxRecordDateTime
  group by a2.Name
  order by RecordDateTime asc;`,

  GetDailyProfits: `select * from shares.dailyprofit WHERE ShareCode=@code`,

  GetCurrentProfits: `with cte as (
      select
        a.ShareCode,
        max(a.AccountingDate) AccountingDate  
      from shares.dailyprofit a group by a.ShareCode
    )
    select b.* from cte
    inner join shares.dailyprofit b on b.ShareCode=cte.ShareCode and b.AccountingDate=cte.AccountingDate`,

  GetDailyProfitTotals: `select
      AccountingDate,
      sum(ProfitRevenue) TotalRevenue
    from shares.dailyprofit 
    group by AccountingDate
    order by AccountingDate asc`
};